#!/usr/bin/env python
# -*- coding: UTF-8 -*-
"""
@Project ：python_learning 
@File ：数据查询与转发.py
@IDE  ：PyCharm 
@Author ：李涵彬
@Date ：2025/2/14 上午11:10 
"""

import configparser
import json
import socket
from multiprocessing import Process

import cx_Oracle  # Oracle 数据库
import pyodbc  # SQL Server 数据库

# 配置文件
CONFIG_FILE = 'database_config.ini'

# 配置监听端口
LISTEN_HOST = '127.0.0.1'  # 监听本地主机
LISTEN_PORT = 12345  # 监听端口

# 返回结果的端口和地址
RESULT_HOST = '127.0.0.1'
RESULT_PORT = 12346  # 返回结果的端口

# 读取数据库配置
config = configparser.ConfigParser()
config.read(CONFIG_FILE)

# Oracle 数据库配置
oracle_config = config['Oracle']
oracle_host = oracle_config['host']
oracle_port = oracle_config['port']
oracle_username = oracle_config['username']
oracle_password = oracle_config['password']
oracle_service_name = oracle_config['service_name']

# SQL Server 配置
sqlserver_config = config['SQLServer']
sqlserver_host = sqlserver_config['host']
sqlserver_port = sqlserver_config['port']
sqlserver_username = sqlserver_config['username']
sqlserver_password = sqlserver_config['password']
sqlserver_database = sqlserver_config['database']


def parse_message(message):
	"""解析客户端发送的报文"""
	# 报文格式为：数据库类型_数据库名_SQL语句
	parts = message.split('_', 2)
	if len(parts) != 3:
		return None
	db_type, db_name, sql_statement = parts
	return db_type.strip(), db_name.strip(), sql_statement.strip()


def connect_oracle():
	"""连接到 Oracle 数据库"""
	dsn = cx_Oracle.makedsn(
		oracle_host,
		oracle_port,
		service_name=oracle_service_name
	)
	return cx_Oracle.connect(
		oracle_username,
		oracle_password,
		dsn
	)


def connect_sqlserver():
	"""连接到 SQL Server 数据库"""
	return pyodbc.connect(
		f"DRIVER={{ODBC Driver 17 for SQL Server}};"
		f"SERVER={sqlserver_host},{sqlserver_port};"
		f"DATABASE={sqlserver_database};"
		f"UID={sqlserver_username};"
		f"PWD={sqlserver_password}"
	)


def execute_sql(db_type, sql, db_conn):
	"""执行 SQL 语句"""
	try:
		cursor = db_conn.cursor()
		cursor.execute(sql)

		# 如果是 SELECT 查询，获取结果
		if sql.lstrip().upper().startswith("SELECT"):
			result = cursor.fetchall()
		else:
			result = None

		# 提交事务后关闭连接
		db_conn.commit()
		cursor.close()
		db_conn.close()

		return {"success": True, "result": result}
	except Exception as e:
		return {"success": False, "error": str(e)}


def handle_client(client_socket, result_host, result_port):
	"""处理客户端请求"""
	try:
		# 接收客户端发送的数据
		data = client_socket.recv(1024).decode('utf-8').strip()
		if not data:
			print("客户端发送了空数据")
			return

		# 解析报文
		parsed = parse_message(data)
		if not parsed:
			response = {"success": False, "error": "报文格式错误"}
		else:
			db_type, db_name, sql = parsed
			print(f"收到报文：数据库类型={db_type}，数据库名={db_name}，SQL语句={sql}")

			# 连接到数据库
			db_conn = None
			if db_type.lower() == 'oracle':
				db_conn = connect_oracle()
			elif db_type.lower() == 'sqlserver':
				db_conn = connect_sqlserver()
			else:
				response = {"success": False, "error": "不支持的数据库类型"}

			# 执行 SQL
			if db_conn:
				response = execute_sql(db_type, sql, db_conn)
			else:
				response = {"success": False, "error": "无法连接到数据库"}

		# 将结果发送回客户端
		try:
			# 重新建立连接到返回端口
			result_socket = socket.socket(socket.AF_INET, socket.SOCK_STREAM)
			result_socket.connect((result_host, result_port))
			result_socket.sendall(json.dumps(response).encode('utf-8'))
			result_socket.close()
		except Exception as e:
			print(f"无法发送结果：{str(e)}")

	finally:
		client_socket.close()


def main():
	# 创建一个 socket 对象用于监听
	server_socket = socket.socket(socket.AF_INET, socket.SOCK_STREAM)
	server_socket.bind((LISTEN_HOST, LISTEN_PORT))
	server_socket.listen(1)

	print(f"服务器正在监听 {LISTEN_HOST}:{LISTEN_PORT}...")

	while True:
		client_socket, addr = server_socket.accept()
		print(f"连接来自: {addr}")

		# 创建子进程处理客户端请求
		process = Process(target=handle_client,
						  args=(client_socket, RESULT_HOST, RESULT_PORT))
		process.start()


if __name__ == "__main__":
	main()
